from collections import OrderedDict
from datetime import datetime
from flask_login import current_user, login_required, login_user, logout_user
from sqlalchemy import text
from flask import Blueprint, redirect, render_template, request, url_for
from utils.utils import User
from extensions import db


web_bp = Blueprint('web', __name__)

@web_bp.route('/')
def index():
    return render_template('index.html')

@web_bp.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        sql = f"""
                SELECT 
                    id, username, password, role
                FROM 
                    users 
                WHERE 
                    username = '{username}' AND password = '{password}'
            """
        result = db.session.execute(text(sql))
        user_data = [dict(row._mapping) for row in result]
        if user_data:
            user_data = user_data[0]
            user_obj = User(user_data['id'], user_data['username'], user_data['password'],user_data['role'])
            login_user(user_obj)
            return redirect(url_for('web.dashboard'))
        
    return render_template('login.html',active_page="login")


@web_bp.route('/users')  # 确保路由装饰器存在
@login_required # 确保用户已登录
def users():  # 函数名决定默认端点名称
    users_id = current_user.id
    # 你的视图逻辑
    sql = f"""
            SELECT * FROM imaotai_user
            WHERE users_id = {users_id}
        """
    result = db.session.execute(text(sql))
    imaotai_users = [dict(row._mapping) for row in result]

    return render_template('./user/users.html',imaotai_users=imaotai_users,active_page="users")


@web_bp.route("/logout")
@login_required  # 确保只有已登录用户能访问
def logout():
    logout_user()  # 清空用户会话
    return redirect(url_for('web.index'))  # 退出后跳转到首页
    

@web_bp.route('/dashboard')  # 正确定义路由
def dashboard():
    return render_template('dashboard.html',active_page = "dashboard")


@web_bp.route("/products")
def products():
    formatted_date = datetime.now().strftime("%Y-%m-%d") 
    sql = f"""
        SELECT 
            * 
        FROM 
            products
        WHERE 
            created_date = '{formatted_date}';
    """

    result = db.session.execute(text(sql))
    products = [dict(row._mapping) for row in result]

    return render_template('./products/products.html',products=products,active_page="products")



@web_bp.route("/imaotailog")
@login_required # 确保用户已登录
def imaotailog():
    users_id = current_user.id
    imaotailogs = []
    sql = f"""
        SELECT 
            id,
            imaotai_user_id,
            imaotai_user_name,
            mobile,
            email 
        FROM 
            imaotai_user
        WHERE 
            users_id = {users_id};

    """
    result = db.session.execute(text(sql))
    imaotai_users = [dict(row._mapping) for row in result]
    for i in imaotai_users:
        data = {}
        sql = f"""
                SELECT 
                    * 
                FROM 
                    imaotai_log
                WHERE 
                    imaotai_user_id='{i['imaotai_user_id']}' 
                    AND created_date like '{datetime.now().strftime("%Y-%m")}%'
                order by created_time desc;
            """
        result = db.session.execute(text(sql))
        log_data = [dict(row._mapping) for row in result]
        
        if log_data:
            data['user'] = i
            data['log'] = log_data
            imaotailogs.append(data)

    # 初始化结果字典
    result = {}

    for user_data in imaotailogs:
        user_info = user_data['user']
        user_id = user_info['imaotai_user_id']
        
        for log_entry in user_data['log']:
            # 直接使用原始日期对象作为键
            date_key = log_entry['created_date']
            
            # 如果日期不存在则初始化
            if date_key not in result:
                result[date_key] = {}
            
            # 如果用户ID不存在则初始化（包含用户信息和日志列表）
            if user_id not in result[date_key]:
                result[date_key][user_id] = {
                    "user": user_info,
                    "log": []
                }
            
            # 添加日志条目（保持原始datetime对象）
            result[date_key][user_id]["log"].append({
                "message": log_entry['message'],
                "created_time": log_entry['created_time']
            })
    # 对日期进行排序（从晚到早）
    sorted_dates = sorted(result.keys(), reverse=True)

    # 创建有序字典，按日期顺序存储
    ordered_result = OrderedDict()
    for date_key in sorted_dates:
        ordered_result[date_key] = result[date_key]

    # 最终结果（日期已排序）
    final_result = dict(ordered_result)
    return render_template('./imaotailog/imaotailog.html',imaotailogs=final_result,active_page="imaotailog")



@web_bp.route("/result")
@login_required # 确保用户已登录
def result():
    users_id = current_user.id
    today = datetime.now().strftime("%Y-%m-%d")
    current_month = datetime.now().strftime("%Y-%m")

    # 获取详细数据
    result_data = [dict(row._mapping) for row in db.session.execute(text(f"""
        SELECT * FROM imaotai_result
        WHERE users_id = {users_id}
        AND created_date like '{current_month}%'
        ORDER BY created_time DESC
    """))]

    # 一次查询获取所有统计数据
    stats = dict(db.session.execute(text(f"""
        SELECT 
            (SELECT COUNT(*) FROM imaotai_result WHERE users_id = {users_id}) as total,
            (SELECT COUNT(*) FROM imaotai_result WHERE users_id = {users_id} AND message like '%申购成功%') as success,
            (SELECT COUNT(*) FROM imaotai_result WHERE users_id = {users_id} AND created_date = '{today}') as today,
            (SELECT COUNT(*) FROM products WHERE created_date = '{today}') as products
    """)).fetchone()._mapping)

    return render_template('./result/result.html',
                         active_page="result",
                         result=result_data,
                         total=stats['total'],
                         success=stats['success'],
                         today=stats['today'],
                         products=stats['products'])